Functoid - DateTimeAdd

DateTimeAdd Functoid

Description: The DateTimeAdd Functoid will interpret almost any date / time format, adjust the time for timezone and daylight savings time and output the data in almost any required format. Background Information: The DateTime Add Functoid is used to interpret date and time fields and translate it it in the right output format. Also a special functionality to anticipate to time zones and Daylight Saving Times is supported. A bit of background information concerning Time Zones and Daylight Saving Times in relation to Service Desk is required. When records containing date and time data are uploaded to Service Desk via the data exchange mechanism, the date and time information in the XML will not be the same as the data and time fields that is written to the database of Service Desk. The actual date and time written in the database is the UTC time. Depending on the Time Zone and the date stamp in the XML, Service Desk will actually adjust the the date and time. This is definitely not a bug in Service Desk but it takes care of that when actual date and times are written, for example for the history lines that everyone, no matter what timezone, will see the actual local time. When this feature was not implemented in Service Desk it might have happened that someone in Australia writes a history line to the CMDB at February 1 at 3:00 PM while in England it is 3:00 AM. Without the UTC correction feature the time would have appeared at a time in the future. Knowing this, we should come up with a resolution for this. An example: The timezone is Amsterdam (+1 UTC) and say the daylight saving is from March 26 - October 29. Record1

• Date and time in the XML is February 1, 2006 - 15:00
• Date and time written to the CMDB is February 1, 2006 - 14:00

Record2

• Date and time in the XML is August 1, 2006 - 15:00
• Date and time written to the CMDB is August 1, 2006 - 13:00

When we compare the original date and time in the source (ie. February 1, 2006 15:00) with the date and time in the CMDB (February 1, 2006, 14:00) there will be a difference of one hour after writing the data to the CMDB. When DM-ETL+ compares these values, the result will be that all records are showing differences and DM-ETL+ will try to update those differences next time again. With the DateTimeAdd Functoid we have found a resolution for this Service Desk functionality as you can see at the explanation of the Functoid below. More info about DateTime format can be found here. Inputs with their allowed values:

• InputDate
  • Any date and time value in almost any possible format
• Format
  • Use y for the year part of the date
  • Use M for the month part of the date
  • Use d for the day part of the date
  • Use H for the hour part of the time
  • Use m for the minutes part of the time
  • Use s for the seconds part of the time
  • Use f for the fraction of seconds
  • Use hh and tt for 12 hour and am or pm notation (capitals are not supported)
• Delta
  • Specify the delta time in years (y), months (M), days (d) hours (h), minutes (m) or seconds (s) This might be 2h to add two hours or -2h to reduce the time with two hours
• OutputFormat
  • Use y for the year part of the date
  • Use M for the month part of the date
  • Use d for the day part of the date
  • Use H for the hour part of the time
  • Use m for the minutes part of the time
  • Use s for the seconds part of the time
  • Use f for the fraction of seconds
  • Use hh and tt for 12 hour and am or pm notation (capitals are not supported)

You can add any part of the output and formatting options as you like. MM/dd/yy hh:00:00 will output the month/day/year and the time with only the real hours and fixed minutes and seconds like 11/30/06 12:00:00

Picture of the DateTimeAdd Functoid

Remark: None. Example:

• InputDate
  • 2006.02.26 12:24:36.111
• Format
  • yyyy.MM.dd HH:mm:ss.fff
• Delta
  • 2h
• OutputFormat
  • dd/MM/yy HH:00:00
• Output
  • 26/02/06 14:00:00

See Figure 1.

Figure1